package cn.outsourcing.supermarket.provider;

import cn.outsourcing.supermarket.common.constants.CommonConstants;
import cn.outsourcing.supermarket.common.constants.PayConstants;
import cn.outsourcing.supermarket.common.enums.OrderTypeEnums;
import cn.outsourcing.supermarket.dto.backstage.BackstageOrdersInfoDTO;
import org.apache.ibatis.jdbc.SQL;

/**
 * 后台首页供应器
 *
 * @author gzkemays
 * @since 2021/10/20 16:56
 */
public class BackstageHomeProvider {

  // FIXME: 分页数据出现差异，外连接不能解决当 order_goods 的问题
  public String getOrdersData(BackstageOrdersInfoDTO orderDto) {
    long page = orderDto.getPage();
    long limit = orderDto.getLimit();
    long start;
    if (page == 1 || page == 0) {
      start = 0;
    } else {
      start = (page - 1) * limit;
    }
    return new SQL() {
      {
        SELECT(
                "ho.*",
                "hog.`goods_name`",
                "hog.`goods_aka`",
                "hog.`list_pic_url`",
                "hog.`number`",
                "hog.`goods_specifition_name_value`",
                "hog.`retail_price`")
            .FROM("hiolabs_order ho")
            .LEFT_OUTER_JOIN("hiolabs_order_goods hog on hog.order_id = ho.id ")
            .WHERE("order_status in (${orderDto.status})")
            .AND()
            .WHERE("order_type !=" + OrderTypeEnums.RECHARGE.getNum())
            .ORDER_BY("ho.add_time DESC");
      }
    }.toString();
  }

  public String getHomeBasicData() {
    return new SQL() {
      {
        SELECT(CommonConstants.PROVIDER_COUNT_SQL + " as `user` ")
            .SELECT("(" + numbersOfShelfItems() + ") as goodsOnsale ")
            .SELECT("(" + numbersOfToBeShipped() + ") as orderToDelivery")
            .FROM("hiolabs_user");
      }
    }.toString();
  }

  /**
   * 获取 num 数
   *
   * @return num 数据
   */
  public String getDetailedList() {
    return new SQL() {
      {
        SELECT("(" + getAddCartCount() + ") as addCart")
            .SELECT("(" + getAddOrderCount() + ") as addOrderNum")
            .SELECT("(" + getAddOrderSum() + ") as addOrderSum")
            .SELECT("(" + getPayOrderNum() + ") as payOrderNum")
            .SELECT("(" + getPayOrderSum() + ") as payOrderSum")
            .FROM("hiolabs_user")
            .LIMIT(1);
      }
    }.toString();
  }

  /**
   * 获取在 {@code time} 时间内注册的用户
   *
   * @return newData , newUser = newData.size()
   */
  public String getNewRegUser() {
    return new SQL() {
      {
        SELECT("*").FROM("hiolabs_user").WHERE("`id` > 0").AND().WHERE("register_time > ${time}");
      }
    }.toString();
  }

  /**
   * 获取 {@code firstTime} 之前注册且 {@code lastTime} 之后登录过的用户。
   *
   * @return oldData , oldUser = oldData.size()
   */
  public String getOldUser() {
    return new SQL() {
      {
        SELECT("*")
            .FROM("hiolabs_user")
            .WHERE("`id` > 0")
            .AND()
            .WHERE("register_time < ${time}")
            .AND()
            .WHERE("last_login_time > ${lastTime}");
      }
    }.toString();
  }

  /**
   * 获取在 time 时间下订单的购物车
   *
   * @return addCart
   */
  public String getAddCartCount() {
    return new SQL() {
      {
        SELECT("COUNT(*) as count")
            .FROM("hiolabs_cart")
            .WHERE("`is_delete` = 0")
            .AND()
            .WHERE("`add_time` > ${time}")
            .LIMIT(1);
      }
    }.toString();
  }
  /**
   * 获取在 time 时间提交的订单
   *
   * @return addOrderNum
   */
  public String getAddOrderCount() {
    return new SQL() {
      {
        SELECT("COUNT(*) as count")
            .FROM("hiolabs_order")
            .WHERE("`is_delete` = 0")
            .AND()
            .WHERE("`add_time` > ${time}")
            .LIMIT(1);
      }
    }.toString();
  }

  /**
   * 获取 time 之内的下单金额
   *
   * @return addOrderSum
   */
  public String getAddOrderSum() {
    return new SQL() {
      {
        SELECT("SUM(`actual_price`) as count")
            .FROM("hiolabs_order")
            .WHERE("`is_delete` = 0")
            .AND()
            .WHERE("`add_time` > ${time}")
            .LIMIT(1);
      }
    }.toString();
  }

  /**
   * 获取 time 时间内成交的订单【支付 - 成交订单数】
   *
   * @return payOrderNum
   */
  public String getPayOrderNum() {
    return new SQL() {
      {
        SELECT("COUNT(*) as count")
            .FROM("hiolabs_order")
            .WHERE("`is_delete` = 0")
            .AND()
            .WHERE("`add_time` > ${time}")
            .AND()
            .WHERE("`order_status` IN (201,802,300,301)")
            .LIMIT(1);
      }
    }.toString();
  }

  /**
   * 获取 time 天之内成交的金额【支付 - 成交金额】
   *
   * @return payOrderSum
   */
  public String getPayOrderSum() {
    return new SQL() {
      {
        SELECT(" SUM(`actual_price`) as count")
            .FROM("hiolabs_order")
            .WHERE("`is_delete` = 0")
            .AND()
            .WHERE("`add_time` > ${time}")
            .AND()
            .WHERE("`order_status` IN (201,802,300,301)")
            .LIMIT(1);
      }
    }.toString();
  }

  /**
   * 上架商品的数量
   *
   * @return 上架商品的 SQL 语句
   */
  public String numbersOfShelfItems() {
    return new SQL() {
      {
        SELECT(CommonConstants.PROVIDER_COUNT_SQL)
            .FROM("hiolabs_goods as goods")
            .WHERE("goods.is_delete = 0");
      }
    }.toString();
  }

  /**
   * 注册会员数量
   *
   * @return 会员总数 SQL 语句
   */
  public String numbersOfUsers() {
    return new SQL() {
      {
        SELECT(CommonConstants.PROVIDER_COUNT_SQL).FROM("hiolabs_user as users");
      }
    }.toString();
  }

  /**
   * 待发货数量
   *
   * @return 待发货 SQL 语句
   */
  public String numbersOfToBeShipped() {
    return new SQL() {
      {
        SELECT(CommonConstants.PROVIDER_COUNT_SQL)
            .FROM("hiolabs_order as orders")
            .WHERE("orders.order_status = " + PayConstants.TO_BE_SHIPPED)
            .AND()
            .WHERE("is_delete = 0");
      }
    }.toString();
  }
}
